package Handlers;

import gui.Main;
import ObjectClass.Address;
import ObjectClass.Cargo;
import ObjectClass.Customer;
import ObjectClass.Distance;
import ObjectClass.Driver;
import ObjectClass.SQLCommunication;
import ObjectClass.Task;
import ObjectClass.Vehicle;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Jeppe
 */
public class TaskHandler {

    private final static String TABEL = "Task";
    private SQLCommunication sqlc;
    private AddressHandler addressHandler;
    private CargoHandler cargoHandler;
    private CustomerHandler customerHandler;
    private DriverHandler driverHandler;
    private VehicleHandler vehicleHandler;
    private DistanceHandler distanceHandler;
    private ArrayList<Task> taskList;

    public TaskHandler(Main main) {
        this.sqlc = main.getSql();
        this.addressHandler = main.getAddressHandler();
        this.cargoHandler = main.getCargoHandler();
        this.customerHandler = main.getCustomerHandler();
        this.driverHandler = main.getDriverHandler();
        this.vehicleHandler = main.getVehicleHandler();
        this.distanceHandler = main.getDistanceHandler();
        taskList = new ArrayList<>();
    }

    private void instantierTaskList() {
        try {
            taskList.clear();
            int taskID;
            int dd;
            int mm;
            int YYYY;
            String description;
            String cargoType;
            int cargoWeight;
            int cargoNumber;
            boolean dangCargo;
            Vehicle vehicle;
            Driver driver;
            Customer customer = null;
            Address pickUpAddress;
            Address deliveryAddress;
            Cargo cargo = null;
            Distance distance = null;
            String sql = "SELECT * FROM " + TABEL;
            ResultSet rs = sqlc.select(TABEL, sql);

            while (rs.next()) {
                taskID = rs.getInt("TaskID");
                dd = rs.getInt("DD");
                mm = rs.getInt("MM");
                YYYY = rs.getInt("YYYY");
                description = rs.getString("Description");
                cargoType = rs.getString("CargoType");
                cargoWeight = rs.getInt("CargoWeight");
                cargoNumber = rs.getInt("CargoNumber");
                dangCargo = rs.getBoolean("DangCargo");
                cargo = cargoHandler.getCargoByID(rs.getInt("CargoID"));
                driver = driverHandler.getDriver(rs.getInt("DriverPhoneNo"));
//                if(!customerHandler.tjekCustomerByPhoneNo(rs.getInt("CustomerPhoneNo"))){
//                    System.out.println("Der er sket en fejl. Customer findes ikke");
//                    Customer enCustomer = new Customer(00, 00000, "Fejl", "Fejl", 00, "Fejl");
//                    customer = enCustomer;
//                }else{
                customer = customerHandler.getCustomerByPhoneNo(rs.getInt("CustomerPhoneNo"));
//                }
                pickUpAddress = addressHandler.getAddressByID(rs.getInt("PickUpAddressID"));
                deliveryAddress = addressHandler.getAddressByID(rs.getInt("DeliveryAddressID"));
                distance = distanceHandler.getDistanceByID(rs.getInt("DistanceID"));
                vehicle = vehicleHandler.getVehicleByLicensePlate(rs.getString("VehicleLicense"));
                Task task = new Task(taskID, dd, mm, YYYY, description, cargoType, cargoWeight, cargoNumber, dangCargo, cargo, driver, customer, pickUpAddress, deliveryAddress, distance, vehicle);
                taskList.add(task);

            }
        } catch (SQLException ex) {
            Logger.getLogger(TaskHandler.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("Der er sket en fejl ved instantiering af TaskList");
        }
//        System.out.println("Antal tasks fundet: " + taskList.size());
    }

    public ArrayList<Task> getTaskList() {
        ArrayList<Task> taskList = new ArrayList<>();
        int taskID;
        int dd;
        int mm;
        int YYYY;
        String description;
        String cargoType;
        int cargoWeight;
        int cargoNumber;
        boolean dangCargo;
        Cargo cargo = null;
        Driver driver;
        Customer customer = null;
        Address pickUpAddress;
        Address deliveryAddress;
        Distance distance = null;
        Vehicle vehicle;
        String sql;
        sql = "SELECT TaskID, DD, MM,YYYY, Description, CargoType,CargoWeight,CargoNumber,DangCargo, "
                + "(SELECT CargoID from cargo where task.CargoID = cargo.ID) as CargoID, "
                + "(SELECT CargoType FROM cargo WHERE task.CargoID = cargo.ID) as CargoType, "
                + "(SELECT cargo.Price FROM cargo WHERE task.CargoID = cargo.ID) as CargoPrice, "
                + "(SELECT cargo.Lower FROM cargo WHERE task.CargoID = cargo.ID) as CargoLower, "
                + "(SELECT cargo.Upper FROM cargo WHERE task.CargoID = cargo.ID) as CargoUpper, "
                + "(SELECT cargo.Cargoweight FROM cargo WHERE task.CargoID = cargo.ID) as CargoWeight, "
                + "(SELECT Driver.PhoneNo FROM driver WHERE task.DriverPhoneNo = driver.PhoneNo) as DriverPhoneNo, "
                + "(SELECT Driver.FName FROM driver WHERE task.DriverPhoneNo = driver.PhoneNo) as DriverFname, "
                + "(SELECT Driver.LName FROM driver WHERE task.DriverPhoneNo = driver.PhoneNo) as DriverLname, "
                + "(SELECT Customer.ID FROM customer WHERE task.CustomerPhoneNo = customer.PhoneNo) as CustomerID, "
                + "(SELECT Customer.PhoneNo FROM customer WHERE task.CustomerPhoneNo = customer.PhoneNo) as CustomerPhoneNo, "
                + "(SELECT Customer.CompanyName FROM customer WHERE task.CustomerPhoneNo = customer.PhoneNo) as CustomerCompanyName, "
                + "(SELECT Customer.Address FROM customer WHERE task.CustomerPhoneNo = customer.PhoneNo) as CustomerAddress, "
                + "(SELECT Customer.Zipcode FROM customer WHERE task.CustomerPhoneNo = customer.PhoneNo) as CustomerZipcode, "
                + "(SELECT Customer.City FROM customer WHERE task.CustomerPhoneNo = customer.PhoneNo) as CustomerCity, "
                + "(SELECT address.ID FROM address WHERE task.PickupAddressID = address.ID) as PickupAddressID, "
                + "(SELECT address.Company FROM address WHERE task.PickupAddressID = address.ID) as PickUpAddressCompany, "
                + "(SELECT address.Address FROM address WHERE task.PickupAddressID = address.ID) as PickUpAddressAddress, "
                + "(SELECT address.Zipcode FROM address WHERE task.PickupAddressID = address.ID) as PickUpAddressZipcode, "
                + "(SELECT address.City FROM address WHERE task.PickupAddressID = address.ID) as PickUpAddressCity, "
                + "(SELECT address.District FROM address WHERE task.PickupAddressID = address.ID) as PickUpAddressDistrict, "
                + "(SELECT address.Country FROM address WHERE task.PickupAddressID = address.ID) as PickUpAddressCountry, "
                + "(SELECT address.ID FROM address WHERE task.DeliveryAddressID = address.ID) as DeliveryAddressID, "
                + "(SELECT address.Company FROM address WHERE task.DeliveryAddressID = address.ID) as DeliveryAddressCompany, "
                + "(SELECT address.Address FROM address WHERE task.DeliveryAddressID= address.ID) as DeliveryAddressAddress, "
                + "(SELECT address.Zipcode FROM address WHERE task.DeliveryAddressID = address.ID) as DeliveryAddressZipcode, "
                + "(SELECT address.City FROM address WHERE task.DeliveryAddressID= address.ID) as DeliveryAddressCity, "
                + "(SELECT address.District FROM address WHERE task.DeliveryAddressID= address.ID) as DeliveryAddressDistrict, "
                + "(SELECT address.Country FROM address WHERE task.DeliveryAddressID = address.ID) as DeliveryAddressCountry, "
                + "(SELECT Distance.ID FROM distance WHERE task.DistanceID = distance.ID) as DistanceID, "
                + "(SELECT distance.StartDestination FROM distance WHERE task.DistanceID = distance.ID) as StartDestination, "
                + "(SELECT distance.EndDestination FROM distance WHERE task.DistanceID = distance.ID) as EndDestination, "
                + "(SELECT Distance.distance FROM distance WHERE task.DistanceID = distance.ID) as Distance, "
                + "(SELECT Vehicle.ID FROM vehicle WHERE task.VehicleLicense = vehicle.LicensePlate) as VehicleID, "
                + "(SELECT Vehicle.LicensePlate FROM vehicle WHERE task.VehicleLicense = vehicle.LicensePlate) as VehicleLicensePlate, "
                + "(SELECT Vehicle.VType FROM vehicle WHERE task.VehicleLicense = vehicle.LicensePlate) as VehicleVType, "
                + "(SELECT Vehicle.MaxNumberOfPallets FROM vehicle WHERE task.VehicleLicense = vehicle.LicensePlate) as VehicleMaxNumberOfPallets, "
                + "(SELECT Vehicle.CubicMeters FROM vehicle WHERE task.VehicleLicense = vehicle.LicensePlate) as VehicleCubicMeters, "
                + "(SELECT Vehicle.MaxWeight FROM vehicle WHERE task.VehicleLicense = vehicle.LicensePlate) as VehicleMaxWeight "
                + "FROM Task ";

        ResultSet rs = sqlc.select(TABEL, sql);
        try {
            while (rs.next()) {
                taskID = rs.getInt("TaskID");
                dd = rs.getInt("DD");
                mm = rs.getInt("MM");
                YYYY = rs.getInt("YYYY");
                description = rs.getString("Description");
                cargoType = rs.getString("CargoType");
                cargoWeight = rs.getInt("CargoWeight");
                cargoNumber = rs.getInt("CargoNumber");
                dangCargo = rs.getBoolean("DangCargo");
                cargo = new Cargo(rs.getInt("CargoID"), rs.getString("CargoType"), rs.getInt("CargoPrice"), rs.getInt("CargoLower"), rs.getInt("CargoUpper"), rs.getInt("CargoWeight"));
                driver = new Driver(rs.getInt("DriverPhoneNo"), rs.getString("DriverFname"), rs.getString("DriverLname"));
                customer = new Customer(rs.getInt("CustomerID"), rs.getInt("CustomerPhoneNo"), rs.getString("CustomerCompanyName"), rs.getString("CustomerAddress"), rs.getString("CustomerZipcode"), rs.getString("CustomerCity"));
                pickUpAddress = new Address(rs.getInt("PickUpAddressID"), rs.getString("PickUpAddressCompany"), rs.getString("PickUpAddressAddress"), rs.getString("PickUpAddressZipcode"), rs.getString("PickUpAddressCity"), rs.getString("PickUpAddressDistrict"), rs.getString("PickUpAddressCountry"));
                deliveryAddress = new Address(rs.getInt("DeliveryAddressID"), rs.getString("DeliveryAddressCompany"), rs.getString("DeliveryAddressAddress"), rs.getString("DeliveryAddressZipcode"), rs.getString("DeliveryAddressCity"), rs.getString("DeliveryAddressDistrict"), rs.getString("DeliveryAddressCountry"));
                distance = new Distance(rs.getInt("DistanceID"), rs.getString("StartDestination"), rs.getString("EndDestination"), rs.getInt("Distance"));
                vehicle = new Vehicle(rs.getInt("VehicleID"), rs.getString("VehicleLicensePlate"), rs.getString("VehicleVType"), rs.getInt("VehicleMaxNumberOfPallets"), rs.getInt("VehicleCubicMeters"), rs.getInt("VehicleMaxWeight"));
                Task task = new Task(taskID, dd, mm, YYYY, description, cargoType, cargoWeight, cargoNumber, dangCargo, cargo, driver, customer, pickUpAddress, deliveryAddress, distance, vehicle);
                taskList.add(task);

            }
        } catch (SQLException ex) {
            Logger.getLogger(TaskHandler.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("Der er sket en fejl ved instantiering af TaskList");
        }
        return taskList;
    }

    public ArrayList<Task> getTaskListByDato(int dd, int mm, int YYYY) {
        ArrayList<Task> demandTaskList = new ArrayList<>();
        int taskID;
        String description;
        String cargoType;
        int cargoWeight;
        int cargoNumber;
        boolean dangCargo;
        Cargo cargo = null;
        Driver driver;
        Customer customer = null;
        Address pickUpAddress;
        Address deliveryAddress;
        Distance distance = null;
        Vehicle vehicle;
        String sql;
        sql = "SELECT TaskID, DD, MM,YYYY, Description, CargoType,CargoWeight,CargoNumber,DangCargo, "
                + "(SELECT CargoID from cargo where task.CargoID = cargo.ID) as CargoID, "
                + "(SELECT CargoType FROM cargo WHERE task.CargoID = cargo.ID) as CargoType, "
                + "(SELECT cargo.Price FROM cargo WHERE task.CargoID = cargo.ID) as CargoPrice, "
                + "(SELECT cargo.Lower FROM cargo WHERE task.CargoID = cargo.ID) as CargoLower, "
                + "(SELECT cargo.Upper FROM cargo WHERE task.CargoID = cargo.ID) as CargoUpper, "
                + "(SELECT cargo.Cargoweight FROM cargo WHERE task.CargoID = cargo.ID) as CargoWeight, "
                + "(SELECT Driver.PhoneNo FROM driver WHERE task.DriverPhoneNo = driver.PhoneNo) as DriverPhoneNo, "
                + "(SELECT Driver.FName FROM driver WHERE task.DriverPhoneNo = driver.PhoneNo) as DriverFname, "
                + "(SELECT Driver.LName FROM driver WHERE task.DriverPhoneNo = driver.PhoneNo) as DriverLname, "
                + "(SELECT Customer.ID FROM customer WHERE task.CustomerPhoneNo = customer.PhoneNo) as CustomerID, "
                + "(SELECT Customer.PhoneNo FROM customer WHERE task.CustomerPhoneNo = customer.PhoneNo) as CustomerPhoneNo, "
                + "(SELECT Customer.CompanyName FROM customer WHERE task.CustomerPhoneNo = customer.PhoneNo) as CustomerCompanyName, "
                + "(SELECT Customer.Address FROM customer WHERE task.CustomerPhoneNo = customer.PhoneNo) as CustomerAddress, "
                + "(SELECT Customer.Zipcode FROM customer WHERE task.CustomerPhoneNo = customer.PhoneNo) as CustomerZipcode, "
                + "(SELECT Customer.City FROM customer WHERE task.CustomerPhoneNo = customer.PhoneNo) as CustomerCity, "
                + "(SELECT address.ID FROM address WHERE task.PickupAddressID = address.ID) as PickupAddressID, "
                + "(SELECT address.Company FROM address WHERE task.PickupAddressID = address.ID) as PickUpAddressCompany, "
                + "(SELECT address.Address FROM address WHERE task.PickupAddressID = address.ID) as PickUpAddressAddress, "
                + "(SELECT address.Zipcode FROM address WHERE task.PickupAddressID = address.ID) as PickUpAddressZipcode, "
                + "(SELECT address.City FROM address WHERE task.PickupAddressID = address.ID) as PickUpAddressCity, "
                + "(SELECT address.District FROM address WHERE task.PickupAddressID = address.ID) as PickUpAddressDistrict, "
                + "(SELECT address.Country FROM address WHERE task.PickupAddressID = address.ID) as PickUpAddressCountry, "
                + "(SELECT address.ID FROM address WHERE task.DeliveryAddressID = address.ID) as DeliveryAddressID, "
                + "(SELECT address.Company FROM address WHERE task.DeliveryAddressID = address.ID) as DeliveryAddressCompany, "
                + "(SELECT address.Address FROM address WHERE task.DeliveryAddressID= address.ID) as DeliveryAddressAddress, "
                + "(SELECT address.Zipcode FROM address WHERE task.DeliveryAddressID = address.ID) as DeliveryAddressZipcode, "
                + "(SELECT address.City FROM address WHERE task.DeliveryAddressID= address.ID) as DeliveryAddressCity, "
                + "(SELECT address.District FROM address WHERE task.DeliveryAddressID= address.ID) as DeliveryAddressDistrict, "
                + "(SELECT address.Country FROM address WHERE task.DeliveryAddressID = address.ID) as DeliveryAddressCountry, "
                + "(SELECT Distance.ID FROM distance WHERE task.DistanceID = distance.ID) as DistanceID, "
                + "(SELECT distance.StartDestination FROM distance WHERE task.DistanceID = distance.ID) as StartDestination, "
                + "(SELECT distance.EndDestination FROM distance WHERE task.DistanceID = distance.ID) as EndDestination, "
                + "(SELECT Distance.distance FROM distance WHERE task.DistanceID = distance.ID) as Distance, "
                + "(SELECT Vehicle.ID FROM vehicle WHERE task.VehicleLicense = vehicle.LicensePlate) as VehicleID, "
                + "(SELECT Vehicle.LicensePlate FROM vehicle WHERE task.VehicleLicense = vehicle.LicensePlate) as VehicleLicensePlate, "
                + "(SELECT Vehicle.VType FROM vehicle WHERE task.VehicleLicense = vehicle.LicensePlate) as VehicleVType, "
                + "(SELECT Vehicle.MaxNumberOfPallets FROM vehicle WHERE task.VehicleLicense = vehicle.LicensePlate) as VehicleMaxNumberOfPallets, "
                + "(SELECT Vehicle.CubicMeters FROM vehicle WHERE task.VehicleLicense = vehicle.LicensePlate) as VehicleCubicMeters, "
                + "(SELECT Vehicle.MaxWeight FROM vehicle WHERE task.VehicleLicense = vehicle.LicensePlate) as VehicleMaxWeight "
                + "FROM Task "
                + "WHERE dd = " + dd + " AND MM = " + mm + " AND YYYY = " + YYYY;

        ResultSet rs = sqlc.select(TABEL, sql);
        try {
            while (rs.next()) {
                taskID = rs.getInt("TaskID");
                dd = rs.getInt("DD");
                mm = rs.getInt("MM");
                YYYY = rs.getInt("YYYY");
                description = rs.getString("Description");
                cargoType = rs.getString("CargoType");
                cargoWeight = rs.getInt("CargoWeight");
                cargoNumber = rs.getInt("CargoNumber");
                dangCargo = rs.getBoolean("DangCargo");
                cargo = new Cargo(rs.getInt("CargoID"), rs.getString("CargoType"), rs.getInt("CargoPrice"), rs.getInt("CargoLower"), rs.getInt("CargoUpper"), rs.getInt("CargoWeight"));
                driver = new Driver(rs.getInt("DriverPhoneNo"), rs.getString("DriverFname"), rs.getString("DriverLname"));
                customer = new Customer(rs.getInt("CustomerID"), rs.getInt("CustomerPhoneNo"), rs.getString("CustomerCompanyName"), rs.getString("CustomerAddress"), rs.getString("CustomerZipcode"), rs.getString("CustomerCity"));
                pickUpAddress = new Address(rs.getInt("PickUpAddressID"), rs.getString("PickUpAddressCompany"), rs.getString("PickUpAddressAddress"), rs.getString("PickUpAddressZipcode"), rs.getString("PickUpAddressCity"), rs.getString("PickUpAddressDistrict"), rs.getString("PickUpAddressCountry"));
                deliveryAddress = new Address(rs.getInt("DeliveryAddressID"), rs.getString("DeliveryAddressCompany"), rs.getString("DeliveryAddressAddress"), rs.getString("DeliveryAddressZipcode"), rs.getString("DeliveryAddressCity"), rs.getString("DeliveryAddressDistrict"), rs.getString("DeliveryAddressCountry"));
                distance = new Distance(rs.getInt("DistanceID"), rs.getString("StartDestination"), rs.getString("EndDestination"), rs.getInt("Distance"));
                vehicle = new Vehicle(rs.getInt("VehicleID"), rs.getString("VehicleLicensePlate"), rs.getString("VehicleVType"), rs.getInt("VehicleMaxNumberOfPallets"), rs.getInt("VehicleCubicMeters"), rs.getInt("VehicleMaxWeight"));
                Task task = new Task(taskID, dd, mm, YYYY, description, cargoType, cargoWeight, cargoNumber, dangCargo, cargo, driver, customer, pickUpAddress, deliveryAddress, distance, vehicle);
                demandTaskList.add(task);

            }
        } catch (SQLException ex) {
            Logger.getLogger(TaskHandler.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("Der er sket en fejl ved hentning af TaskList by dato.");
        }
        return demandTaskList;
    }

    public ArrayList<Task> getTaskListByPhoneNo(int customerPhoneNumber) {
        ArrayList<Task> demandTaskList = new ArrayList<>();
        int taskID;
        int dd;
        int mm;
        int YYYY;
        String description;
        String cargoType;
        int cargoWeight;
        int cargoNumber;
        boolean dangCargo;
        Cargo cargo = null;
        Driver driver;
        Customer customer = null;
        Address pickUpAddress;
        Address deliveryAddress;
        Distance distance = null;
        Vehicle vehicle;
        String sql;
        sql = "SELECT TaskID, DD, MM,YYYY, Description, CargoType,CargoWeight,CargoNumber,DangCargo, "
                + "(SELECT CargoID from cargo where task.CargoID = cargo.ID) as CargoID, "
                + "(SELECT CargoType FROM cargo WHERE task.CargoID = cargo.ID) as CargoType, "
                + "(SELECT cargo.Price FROM cargo WHERE task.CargoID = cargo.ID) as CargoPrice, "
                + "(SELECT cargo.Lower FROM cargo WHERE task.CargoID = cargo.ID) as CargoLower, "
                + "(SELECT cargo.Upper FROM cargo WHERE task.CargoID = cargo.ID) as CargoUpper, "
                + "(SELECT cargo.Cargoweight FROM cargo WHERE task.CargoID = cargo.ID) as CargoWeight, "
                + "(SELECT Driver.PhoneNo FROM driver WHERE task.DriverPhoneNo = driver.PhoneNo) as DriverPhoneNo, "
                + "(SELECT Driver.FName FROM driver WHERE task.DriverPhoneNo = driver.PhoneNo) as DriverFname, "
                + "(SELECT Driver.LName FROM driver WHERE task.DriverPhoneNo = driver.PhoneNo) as DriverLname, "
                + "(SELECT Customer.ID FROM customer WHERE task.CustomerPhoneNo = customer.PhoneNo) as CustomerID, "
                + "(SELECT Customer.PhoneNo FROM customer WHERE task.CustomerPhoneNo = customer.PhoneNo) as CustomerPhoneNo, "
                + "(SELECT Customer.CompanyName FROM customer WHERE task.CustomerPhoneNo = customer.PhoneNo) as CustomerCompanyName, "
                + "(SELECT Customer.Address FROM customer WHERE task.CustomerPhoneNo = customer.PhoneNo) as CustomerAddress, "
                + "(SELECT Customer.Zipcode FROM customer WHERE task.CustomerPhoneNo = customer.PhoneNo) as CustomerZipcode, "
                + "(SELECT Customer.City FROM customer WHERE task.CustomerPhoneNo = customer.PhoneNo) as CustomerCity, "
                + "(SELECT address.ID FROM address WHERE task.PickupAddressID = address.ID) as PickupAddressID, "
                + "(SELECT address.Company FROM address WHERE task.PickupAddressID = address.ID) as PickUpAddressCompany, "
                + "(SELECT address.Address FROM address WHERE task.PickupAddressID = address.ID) as PickUpAddressAddress, "
                + "(SELECT address.Zipcode FROM address WHERE task.PickupAddressID = address.ID) as PickUpAddressZipcode, "
                + "(SELECT address.City FROM address WHERE task.PickupAddressID = address.ID) as PickUpAddressCity, "
                + "(SELECT address.District FROM address WHERE task.PickupAddressID = address.ID) as PickUpAddressDistrict, "
                + "(SELECT address.Country FROM address WHERE task.PickupAddressID = address.ID) as PickUpAddressCountry, "
                + "(SELECT address.ID FROM address WHERE task.DeliveryAddressID = address.ID) as DeliveryAddressID, "
                + "(SELECT address.Company FROM address WHERE task.DeliveryAddressID = address.ID) as DeliveryAddressCompany, "
                + "(SELECT address.Address FROM address WHERE task.DeliveryAddressID= address.ID) as DeliveryAddressAddress, "
                + "(SELECT address.Zipcode FROM address WHERE task.DeliveryAddressID = address.ID) as DeliveryAddressZipcode, "
                + "(SELECT address.City FROM address WHERE task.DeliveryAddressID= address.ID) as DeliveryAddressCity, "
                + "(SELECT address.District FROM address WHERE task.DeliveryAddressID= address.ID) as DeliveryAddressDistrict, "
                + "(SELECT address.Country FROM address WHERE task.DeliveryAddressID = address.ID) as DeliveryAddressCountry, "
                + "(SELECT Distance.ID FROM distance WHERE task.DistanceID = distance.ID) as DistanceID, "
                + "(SELECT distance.StartDestination FROM distance WHERE task.DistanceID = distance.ID) as StartDestination, "
                + "(SELECT distance.EndDestination FROM distance WHERE task.DistanceID = distance.ID) as EndDestination, "
                + "(SELECT Distance.distance FROM distance WHERE task.DistanceID = distance.ID) as Distance, "
                + "(SELECT Vehicle.ID FROM vehicle WHERE task.VehicleLicense = vehicle.LicensePlate) as VehicleID, "
                + "(SELECT Vehicle.LicensePlate FROM vehicle WHERE task.VehicleLicense = vehicle.LicensePlate) as VehicleLicensePlate, "
                + "(SELECT Vehicle.VType FROM vehicle WHERE task.VehicleLicense = vehicle.LicensePlate) as VehicleVType, "
                + "(SELECT Vehicle.MaxNumberOfPallets FROM vehicle WHERE task.VehicleLicense = vehicle.LicensePlate) as VehicleMaxNumberOfPallets, "
                + "(SELECT Vehicle.CubicMeters FROM vehicle WHERE task.VehicleLicense = vehicle.LicensePlate) as VehicleCubicMeters, "
                + "(SELECT Vehicle.MaxWeight FROM vehicle WHERE task.VehicleLicense = vehicle.LicensePlate) as VehicleMaxWeight "
                + "FROM Task "
                + "WHERE CustomerPhoneNo = "+customerPhoneNumber;

        ResultSet rs = sqlc.select(TABEL, sql);
        try {
            while (rs.next()) {
                taskID = rs.getInt("TaskID");
                dd = rs.getInt("DD");
                mm = rs.getInt("MM");
                YYYY = rs.getInt("YYYY");
                description = rs.getString("Description");
                cargoType = rs.getString("CargoType");
                cargoWeight = rs.getInt("CargoWeight");
                cargoNumber = rs.getInt("CargoNumber");
                dangCargo = rs.getBoolean("DangCargo");
                cargo = new Cargo(rs.getInt("CargoID"), rs.getString("CargoType"), rs.getInt("CargoPrice"), rs.getInt("CargoLower"), rs.getInt("CargoUpper"), rs.getInt("CargoWeight"));
                driver = new Driver(rs.getInt("DriverPhoneNo"), rs.getString("DriverFname"), rs.getString("DriverLname"));
                customer = new Customer(rs.getInt("CustomerID"), rs.getInt("CustomerPhoneNo"), rs.getString("CustomerCompanyName"), rs.getString("CustomerAddress"), rs.getString("CustomerZipcode"), rs.getString("CustomerCity"));
                pickUpAddress = new Address(rs.getInt("PickUpAddressID"), rs.getString("PickUpAddressCompany"), rs.getString("PickUpAddressAddress"), rs.getString("PickUpAddressZipcode"), rs.getString("PickUpAddressCity"), rs.getString("PickUpAddressDistrict"), rs.getString("PickUpAddressCountry"));
                deliveryAddress = new Address(rs.getInt("DeliveryAddressID"), rs.getString("DeliveryAddressCompany"), rs.getString("DeliveryAddressAddress"), rs.getString("DeliveryAddressZipcode"), rs.getString("DeliveryAddressCity"), rs.getString("DeliveryAddressDistrict"), rs.getString("DeliveryAddressCountry"));
                distance = new Distance(rs.getInt("DistanceID"), rs.getString("StartDestination"), rs.getString("EndDestination"), rs.getInt("Distance"));
                vehicle = new Vehicle(rs.getInt("VehicleID"), rs.getString("VehicleLicensePlate"), rs.getString("VehicleVType"), rs.getInt("VehicleMaxNumberOfPallets"), rs.getInt("VehicleCubicMeters"), rs.getInt("VehicleMaxWeight"));
                Task task = new Task(taskID, dd, mm, YYYY, description, cargoType, cargoWeight, cargoNumber, dangCargo, cargo, driver, customer, pickUpAddress, deliveryAddress, distance, vehicle);
                demandTaskList.add(task);

            }
        } catch (SQLException ex) {
            Logger.getLogger(TaskHandler.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("Der er sket en fejl ved instantiering af TaskList");
        }
        return demandTaskList;
    }

    public boolean tjekTask(Task task){
        boolean exists = false;
        String sql;
        sql = "SELECT TaskID, DD, MM,YYYY, Description, CargoType,CargoWeight,CargoNumber,DangCargo, "
                + "(SELECT CargoID from cargo where task.CargoID = cargo.ID) as CargoID, "
                + "(SELECT CargoType FROM cargo WHERE task.CargoID = cargo.ID) as CargoType, "
                + "(SELECT cargo.Price FROM cargo WHERE task.CargoID = cargo.ID) as CargoPrice, "
                + "(SELECT cargo.Lower FROM cargo WHERE task.CargoID = cargo.ID) as CargoLower, "
                + "(SELECT cargo.Upper FROM cargo WHERE task.CargoID = cargo.ID) as CargoUpper, "
                + "(SELECT cargo.Cargoweight FROM cargo WHERE task.CargoID = cargo.ID) as CargoWeight, "
                + "(SELECT Driver.PhoneNo FROM driver WHERE task.DriverPhoneNo = driver.PhoneNo) as DriverPhoneNo, "
                + "(SELECT Driver.FName FROM driver WHERE task.DriverPhoneNo = driver.PhoneNo) as DriverFname, "
                + "(SELECT Driver.LName FROM driver WHERE task.DriverPhoneNo = driver.PhoneNo) as DriverLname, "
                + "(SELECT Customer.ID FROM customer WHERE task.CustomerPhoneNo = customer.PhoneNo) as CustomerID, "
                + "(SELECT Customer.PhoneNo FROM customer WHERE task.CustomerPhoneNo = customer.PhoneNo) as CustomerPhoneNo, "
                + "(SELECT Customer.CompanyName FROM customer WHERE task.CustomerPhoneNo = customer.PhoneNo) as CustomerCompanyName, "
                + "(SELECT Customer.Address FROM customer WHERE task.CustomerPhoneNo = customer.PhoneNo) as CustomerAddress, "
                + "(SELECT Customer.Zipcode FROM customer WHERE task.CustomerPhoneNo = customer.PhoneNo) as CustomerZipcode, "
                + "(SELECT Customer.City FROM customer WHERE task.CustomerPhoneNo = customer.PhoneNo) as CustomerCity, "
                + "(SELECT address.ID FROM address WHERE task.PickupAddressID = address.ID) as PickupAddressID, "
                + "(SELECT address.Company FROM address WHERE task.PickupAddressID = address.ID) as PickUpAddressCompany, "
                + "(SELECT address.Address FROM address WHERE task.PickupAddressID = address.ID) as PickUpAddressAddress, "
                + "(SELECT address.Zipcode FROM address WHERE task.PickupAddressID = address.ID) as PickUpAddressZipcode, "
                + "(SELECT address.City FROM address WHERE task.PickupAddressID = address.ID) as PickUpAddressCity, "
                + "(SELECT address.Country FROM address WHERE task.PickupAddressID = address.ID) as PickUpAddressCountry, "
                + "(SELECT address.ID FROM address WHERE task.DeliveryAddressID = address.ID) as DeliveryAddressID, "
                + "(SELECT address.Company FROM address WHERE task.DeliveryAddressID = address.ID) as DeliveryAddressCompany, "
                + "(SELECT address.Address FROM address WHERE task.DeliveryAddressID= address.ID) as DeliveryAddressAddress, "
                + "(SELECT address.Zipcode FROM address WHERE task.DeliveryAddressID = address.ID) as DeliveryAddressZipcode, "
                + "(SELECT address.City FROM address WHERE task.DeliveryAddressID= address.ID) as DeliveryAddressCity, "
                + "(SELECT address.Country FROM address WHERE task.DeliveryAddressID = address.ID) as DeliveryAddressCountry, "
                + "(SELECT Distance.ID FROM distance WHERE task.DistanceID = distance.ID) as DistanceID, "
                + "(SELECT distance.StartDestination FROM distance WHERE task.DistanceID = distance.ID) as StartDestination, "
                + "(SELECT distance.EndDestination FROM distance WHERE task.DistanceID = distance.ID) as EndDestination, "
                + "(SELECT Distance.distance FROM distance WHERE task.DistanceID = distance.ID) as Distance, "
                + "(SELECT Vehicle.ID FROM vehicle WHERE task.VehicleLicense = vehicle.LicensePlate) as VehicleID, "
                + "(SELECT Vehicle.LicensePlate FROM vehicle WHERE task.VehicleLicense = vehicle.LicensePlate) as VehicleLicensePlate, "
                + "(SELECT Vehicle.VType FROM vehicle WHERE task.VehicleLicense = vehicle.LicensePlate) as VehicleVType, "
                + "(SELECT Vehicle.MaxNumberOfPallets FROM vehicle WHERE task.VehicleLicense = vehicle.LicensePlate) as VehicleMaxNumberOfPallets, "
                + "(SELECT Vehicle.CubicMeters FROM vehicle WHERE task.VehicleLicense = vehicle.LicensePlate) as VehicleCubicMeters, "
                + "(SELECT Vehicle.MaxWeight FROM vehicle WHERE task.VehicleLicense = vehicle.LicensePlate) as VehicleMaxWeight "
                + "FROM Task "
                + "WHERE TaskID = " + task.getTaskID();
        
        ResultSet rs = sqlc.select(TABEL, sql);
        
        try{
            while(rs.next()){
                exists = true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(TaskHandler.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("Kunne ikke finde nogen tasks med som svarer til dette taskObjekt: \n"+task.toStringInfo());
        }
        return exists;
    }

    public void insertToDB(Task task) {
        String sql;
        sql = "INSERT into " + TABEL + " (DD, MM, YYYY, Description, CargoType, CargoWeight, CargoNumber, DangCargo, CargoID, DriverphoneNo, CustomerPhoneNo, pickupAddressID, deliveryAddressID, DistanceID, VehicleLicense) values " + task.toStringSQL();
//        if (tjekTask(task)) {
//            System.out.println("Der findes allerede en opgave med de oplysninger");
//        } else {
            sqlc.insert(TABEL, sql);
//            System.out.println("Task er tilføjet");
//        }
    }

    public void delete(Task task) {
        String sql;
        sql = "delete from " + TABEL + " where TaskID = " + task.getTaskID();
        if (tjekTask(task)) {
            sqlc.delete(TABEL, sql);
        }
    }
}
